﻿


CREATE PROCEDURE [dbo].[GenListaBalanta]
	@pAn smallint   
AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @jurnal int
DECLARE @dataEndFunctie varchar(50)
DECLARE @ultimaData datetime
DECLARE @ptgc bit


--determina ultima data la care s-a lucrat
SELECT @ultimaData= MAX(DataDoc) FROM RulajeDoc


SET @categorie = 0
SET @analitica = 0
SET @cfm = 0
SET @ptgc = 0


DECLARE @tipb as varchar(100)
DECLARE @idLista as int;
SET @idLista = 1

DECLARE @CodUnitate smallint
SET @CodUnitate=0

SET @tipb='0'
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
PRINT('CodUnitate ''' + cast(@CodUnitate as varchar(20)) + '''')
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TPB' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
IF @tipb='Extra Bilantiera'
BEGIN
	SET @categorie = 2			
END

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='ANL' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @analitica =1
SET @tipb='0'
--conturi fara miscari
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @cfm =1

SET @jurnal= -1
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb<>''
	SELECT @jurnal = IDJurnal FROM [Jurnale] WHERE Codificare=@tipb
 
print(@jurnal)
SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='LDT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SET @strDataEnd = RTRIM(LTRIM(@strDataEnd))

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TGC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @ptgc =1



IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
	SET @dataEndFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-'+CAST(DAY(@dtDataEndUI) AS varchar(2))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-01'''
    SET @dtDataStartUI = DATEADD(Day, 1-DAY(@dtDataEndUI),@dtDataEndUI)
END
ELSE
BEGIN
	SET @dataEndFunctie = ''''+CAST(@ultimaData AS varchar(50))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@ultimaData) as varchar(4))+'-'+CAST(MONTH(@ultimaData) AS varchar(2))+'-01'''
END


SELECT @strContStart=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SELECT @strContEnd=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
--IF @strContEnd = ''
 -- SET @strContEnd = REPLICATE('Z',50)


DECLARE	@return_value int
PRINT('DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @jurnal int
DECLARE @dataEndFunctie varchar(50)
DECLARE @ultimaData datetime
')
PRINT('SET @DtDataStartUI = ''' + cast(@DtDataStartUI as varchar(20)) + '''')
PRINT('SET @dtDataEndUI=' + cast(@dtDataEndUI as varchar(100)) + '''')
PRINT('SET @jurnal=' + cast(@jurnal as varchar(100)))
PRINT('SET @categorie=' + cast(@categorie as varchar(100)))
PRINT('SET @strContStart=''' + cast(@strContStart as varchar(100)) +'''')
PRINT('SET @StrContEnd =' + cast(@StrContEnd as varchar(100)) + '''')
PRINT('SET @cfm=' + cast(@cfm as varchar(100)))
PRINT('SET @analitica=' + cast(@analitica as varchar(100)))

EXEC	@return_value = [dbo].[GetBalanta]
		@pDtDataStartUI = @DtDataStartUI,
		@pDtDataEndUI = @dtDataEndUI,
		@pJurnal = @jurnal,
		@pCategorie = @categorie,
		@pStrContStart = @strContStart,
		@pStrContEnd = @StrContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0,
		@An = @pAn,
		@Tgc=@ptgc


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
Gata: